'''-- 微信小程序用户表
CREATE TABLE IF NOT EXISTS wx_user (
    user_id SERIAL PRIMARY KEY,
    open_id VARCHAR(50) NOT NULL UNIQUE, -- 用户在微信小程序中的唯一标识
    unionid VARCHAR(50), -- 用户在开放平台的唯一标识 没有绑定开放平台的小程序时为null
    phone_number VARCHAR(50), -- 用户手机号
    wx_name VARCHAR(50), -- 用户昵称
    wx_avatar VARCHAR(200), -- 用户头像 URL
    wx_id VARCHAR(50),--微信号
    create_time TIMESTAMP DEFAULT now() -- 用户注册时间
);'''

import datetime
import logging
import psycopg2


logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
class WxUserTableMethods: 

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    def insert_user(self, open_id, unionid=None, phone_number=None):

        sql = "INSERT INTO wx_user (open_id, unionid, phone_number) VALUES (%s, %s, %s)"

        try:
            self.cursor.execute(sql, (open_id, unionid, phone_number))

        except psycopg2.IntegrityError as e:

            msg =  f"already exists: open_id={open_id}"
            logger.info(msg)

        else:
            self.conn.commit()
            logger.info(f"insert user: open_id={open_id}, unionid={unionid}, phone_number={phone_number}")

    def update_user(self, open_id, wx_name=None, wx_avatar=None, wx_id=None):

        sql = "UPDATE wx_user SET wx_name = %s, wx_avatar = %s, wx_id = %s WHERE open_id = %s"

        self.cursor.execute(sql, ( wx_name, wx_avatar, wx_id, open_id))
        self.conn.commit()
        logger.info(f"update user: open_id={open_id}, wx_name={wx_name}, wx_avatar={wx_avatar}, wx_id={wx_id}")


    def get_user_by_open_id(self, open_id):

        sql = "SELECT * FROM wx_user WHERE open_id = %s"

        self.cursor.execute(sql, (open_id,))
        row = self.cursor.fetchone()

        if row:
            return row
        else:
            return None
        
    def get_user_by_user_id_by_open_id(self, open_id):


        sql = "SELECT user_id FROM wx_user WHERE open_id = %s"

        self.cursor.execute(sql, (open_id,))
        row = self.cursor.fetchone()

        if row:
            return row["user_id"]
        
        else:
            sql = "INSERT INTO wx_user (open_id) VALUES (%s) RETURNING user_id"
            self.cursor.execute(sql, (open_id,))
            self.conn.commit()
            row = self.cursor.fetchone()
            return row["user_id"]

    def get_user_by_unionid(self, unionid):

        sql = "SELECT * FROM wx_user WHERE unionid = %s"

        self.cursor.execute(sql, (unionid,))
        row = self.cursor.fetchone()

        if row:
            return row
        else:
            return None

    def get_user_by_phone_number(self, phone_number):

        sql = "SELECT * FROM wx_user WHERE phone_number = %s"

        self.cursor.execute(sql, (phone_number,))
        row = self.cursor.fetchone()

        if row:
            row["create_time"] = datetime.datetime.strftime(row["create_time"], '%Y-%m-%d %H:%M:%S')
            return row
        else:
            return None
        
    def get_phone_number_by_open_id(self, open_id):

        sql = "SELECT phone_number FROM wx_user WHERE open_id = %s"

        self.cursor.execute(sql, (open_id,))
        row = self.cursor.fetchone()

        if not row:
            return None
        else:
            return row["phone_number"]

    
        

    def update_user_phone_number(self, open_id, phone_number):

        sql = "UPDATE wx_user SET phone_number = %s WHERE open_id = %s"

        self.cursor.execute(sql, (phone_number, open_id))
        self.conn.commit()
        logger.info(f"update user phone_number: open_id={open_id}, phone_number={phone_number}")

    def update_user_unionid(self, open_id, unionid):

        sql = "UPDATE wx_user SET unionid = %s WHERE open_id = %s"

        self.cursor.execute(sql, (unionid, open_id))
        self.conn.commit()
        logger.info(f"update user unionid: open_id={open_id}, unionid={unionid}")
        

    def get_wx_user_info_by_user_id(self, user_id):

        sql = "SELECT * FROM wx_user WHERE user_id = %s"

        self.cursor.execute(sql, (user_id,))
        row = self.cursor.fetchone()

        if row:
            return row
        else:
            return None
        
    def get_wx_user_info_by_user_id_list(self, user_id_list):

        if not user_id_list:
            return []

        sql = "SELECT * FROM wx_user WHERE user_id IN %s"

        self.cursor.execute(sql, (tuple(user_id_list),))
        rows = self.cursor.fetchall()

        return rows
    
    def get_all_wx_user_info(self, limit=10, page=1):


        sql = "SELECT * FROM wx_user order by create_time desc limit %s offset %s"


        self.cursor.execute(sql, (limit, (page-1)*limit))

        result = self.cursor.fetchall()

        for row in result:
            row["create_time"] = datetime.datetime.strftime(row["create_time"], '%Y-%m-%d %H:%M:%S')

        return result


    
    def get_all_wx_user_count(self):

        sql = "SELECT COUNT(*) FROM wx_user"

        self.cursor.execute(sql)

        return self.cursor.fetchone()["count"]
    

    def select_wx_user(self, user_id, open_id, unionid, phone_number, wx_name, wx_avatar, wx_id, limit=10, page=1):

        init_params = [user_id, open_id, unionid, phone_number, wx_name, wx_avatar, wx_id]

        sql = "SELECT * FROM wx_user"

        if user_id:
            sql += " WHERE user_id = %s"

        if open_id:
            if user_id:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " open_id = %s"

        if unionid:
            if user_id or open_id:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " unionid = %s"

        if phone_number:
            if user_id or open_id or unionid:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " phone_number = %s"


        if wx_name:
            if user_id or open_id or unionid or phone_number:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " wx_name = %s"


        if wx_avatar:
            if user_id or open_id or unionid or phone_number or wx_name:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " wx_avatar = %s"


        if wx_id:
            if user_id or open_id or unionid or phone_number or wx_name or wx_avatar:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " wx_id = %s"

        sql += " order by create_time desc limit %s offset %s"

        params = []

        for i in init_params:
            if i:
                params.append(i)

        params.append(limit)
        params.append((page-1)*limit)

        self.cursor.execute(sql, tuple(params))

        result = self.cursor.fetchall()

        for row in result:
            row["create_time"] = datetime.datetime.strftime(row["create_time"], '%Y-%m-%d %H:%M:%S')

        return result
    
    def select_wx_user_count(self, user_id, open_id, unionid, phone_number, wx_name, wx_avatar, wx_id):

        init_params = [user_id, open_id, unionid, phone_number, wx_name, wx_avatar, wx_id]

        sql = "SELECT COUNT(*) FROM wx_user"

        if user_id:
            sql += " WHERE user_id = %s"

        if open_id:
            if user_id:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " open_id = %s"

        if unionid:
            if user_id or open_id:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " unionid = %s"

        if phone_number:
            if user_id or open_id or unionid:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " phone_number = %s"


        if wx_name:
            if user_id or open_id or unionid or phone_number:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " wx_name = %s"


        if wx_avatar:
            if user_id or open_id or unionid or phone_number or wx_name:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " wx_avatar = %s"


        if wx_id:
            if user_id or open_id or unionid or phone_number or wx_name or wx_avatar:
                sql += " AND"
            else:
                sql += " WHERE"
            sql += " wx_id = %s"

        params = []

        for i in init_params:
            if i:
                params.append(i)

        self.cursor.execute(sql, tuple(params))

        result = self.cursor.fetchone()

        return result["count"]



